﻿namespace CloudSql.Client
{
    using System;
    using System.Data;
    using System.Data.SqlClient;
    using System.Linq;
    using System.Xml.Linq;
    using Microsoft.SqlServer.Server;
    using System.Configuration;

    /// <summary>
    /// Primary class for SQL CLR
    /// </summary>
    public class AuditRowDataSource
    {
        /// <summary>
        /// Adds a row to the cloud from SQL CLR
        /// </summary>
        /// <param name="rowSpecification">
        /// xml fragment containing the tablename, schema and primary key
        /// </param>
        [SqlProcedure]
        public static void AddAuditRow(string rowSpecification)
        {
            var sqlConnection = new SqlConnection("context connection=true");
            sqlConnection.Open();
            var rowKey = string.Empty;
            XElement rowData = RenderRowAsXElement(sqlConnection, rowSpecification, ref rowKey);
            sqlConnection.Close();
            var auditRowService = new AuditRowService.AuditRowService();
            ////auditRowService.Url = "http://auditrows.cloudapp.net/AuditRowService.svc";
            auditRowService.Url = GetServiceUrl(new SqlConnection("context connection=true"));
            auditRowService.AddAuditRow("P1", rowKey, rowKey, rowData.ToString());
        }

        /// <summary>
        /// Gets a row from Azure service and sends to SQL via sql pipe
        /// </summary>
        /// <param name="rowSpecification">
        /// xml fragment containing the tablename, schema and primary key
        /// </param>
        public static void GetAuditRow(string rowSpecification)
        {
            XElement rowSpecificationXml = XElement.Parse(rowSpecification);
            var tableName = rowSpecificationXml.Attribute("tableName").Value;
            string tableSchema = rowSpecificationXml.Attribute("tableSchema").Value;
            string rowKey = String.Format("{0}.{1}-", tableSchema, tableName);
            int i = 0;
            foreach (XElement key in rowSpecificationXml.Elements("keys").Elements())
            {
                rowKey += key.Attribute("value").Value;
                i++;
                rowKey += i < rowSpecificationXml.Elements("keys").Elements().Count() ? "-" : string.Empty;
            }

            var auditRowService = new AuditRowService.AuditRowService();
            ////auditRowService.Url = "http://auditrows.cloudapp.net/AuditRowService.svc";
            var sqlConnection = new SqlConnection("context connection=true");
            sqlConnection.Open();
            auditRowService.Url = GetServiceUrl(sqlConnection);

            XElement allData = XElement.Parse(auditRowService.GetAuditRowData("P1", rowKey));

            var sqlMetaData = new SqlMetaData[allData.Element("rowData").Elements("fieldValue").Count() + 1];
            int t = 0;
            foreach (XElement fieldElement in allData.Element("rowData").Elements("fieldValue"))
            {
                if ((SqlDbType)Enum.Parse(typeof(SqlDbType), fieldElement.Attribute("dataTypeName").Value, true) == SqlDbType.NVarChar)
                {
                    sqlMetaData[t] = new SqlMetaData(
                        fieldElement.Attribute("fieldName").Value,
                        (SqlDbType)Enum.Parse(typeof(SqlDbType), fieldElement.Attribute("dataTypeName").Value, true), 
                        255);
                }
                else
                {
                    sqlMetaData[t] = new SqlMetaData(
                        fieldElement.Attribute("fieldName").Value,
                        (SqlDbType)Enum.Parse(typeof(SqlDbType), fieldElement.Attribute("dataTypeName").Value, true));
                }
                t++;
            }
            sqlMetaData[t] = new SqlMetaData("XmlOutput", SqlDbType.NVarChar, 999);

            var sqlDataRecord = new SqlDataRecord(sqlMetaData);
            SqlContext.Pipe.SendResultsStart(sqlDataRecord);

            t = 0;
            foreach (XElement fieldElement in allData.Element("rowData").Elements("fieldValue"))
            {
                if (fieldElement.Attribute("value").Value != "DBNull")
                {
                    if ((SqlDbType)Enum.Parse(typeof(SqlDbType), fieldElement.Attribute("dataTypeName").Value, true) == SqlDbType.NVarChar
                        || (SqlDbType)Enum.Parse(typeof(SqlDbType), fieldElement.Attribute("dataTypeName").Value, true) == SqlDbType.VarChar)
                    {
                        sqlDataRecord.SetSqlString(t, fieldElement.Attribute("value").Value);
                    }
                    else
                    {
                        if ((SqlDbType)Enum.Parse(typeof(SqlDbType), fieldElement.Attribute("dataTypeName").Value, true) == SqlDbType.Int)
                        {
                            sqlDataRecord.SetSqlInt32(t, Convert.ToInt32(fieldElement.Attribute("value").Value));
                        }
                        else
                        {
                            if ((SqlDbType)Enum.Parse(typeof(SqlDbType), fieldElement.Attribute("dataTypeName").Value, true) == SqlDbType.DateTime2)
                            {
                                sqlDataRecord.SetSqlDateTime(t, Convert.ToDateTime(fieldElement.Attribute("value").Value));
                            }
                        }
                    }
                }
   
                t++;
            }

            sqlDataRecord.SetSqlString(t, allData.ToString());
            SqlContext.Pipe.SendResultsRow(sqlDataRecord);
            SqlContext.Pipe.SendResultsEnd();
        }

        /// <summary>
        /// Creates a select statement for the row against the connection and renders the enire row as xml
        /// </summary>
        /// <param name="sqlConnection">
        /// Current (open) connection
        /// </param>
        /// <param name="rowSpecification">
        /// xml fragment containing the tablename, schema and primary key
        /// </param>
        /// <param name="rowKey">
        /// Row key needed for storage service
        /// </param>
        /// <returns>
        /// xml fragment of row schema and data
        /// </returns>
        public static XElement RenderRowAsXElement(SqlConnection sqlConnection, string rowSpecification, ref string rowKey)
        {
            XElement rowSpecificationXml = XElement.Parse(rowSpecification);
            string tableName = rowSpecificationXml.Attribute("tableName").Value;
            string tableSchema = rowSpecificationXml.Attribute("tableSchema").Value;
            rowKey = String.Format("{0}.{1}-", tableSchema, tableName);
            string selectStatement = String.Format("SELECT * FROM {0}.{1} WHERE ", tableSchema, tableName);
            int i = 0;
            foreach (XElement key in rowSpecificationXml.Elements("keys").Elements())
            {
                rowKey += key.Attribute("value").Value;
                selectStatement += String.Format("{0} = {1}", key.Attribute("fieldName").Value, key.Attribute("value").Value);
                i++;
                selectStatement += i < rowSpecificationXml.Elements("keys").Elements().Count() ? " AND " : " ";
                rowKey += i < rowSpecificationXml.Elements("keys").Elements().Count() ? "-" : string.Empty;
            }

            var rowData = new XElement("rowData");

            var sqlCommand = new SqlCommand(selectStatement, sqlConnection);
            sqlCommand.CommandType = CommandType.Text;
            SqlDataReader sqlDataReader = sqlCommand.ExecuteReader(CommandBehavior.KeyInfo);
            if (sqlDataReader != null)
            {
                sqlDataReader.Read();
                for (int t = 0; t < sqlDataReader.VisibleFieldCount; t++)
                {
                    rowData.Add(new XElement("fieldValue",
                                             new XAttribute("fieldName", sqlDataReader.GetName(t)),
                                             new XAttribute("dataType", sqlDataReader.GetSqlValue(t).GetType().ToString()),
                                             new XAttribute("value", sqlDataReader.IsDBNull(t) ? "DBNull" : sqlDataReader.GetSqlValue(t).ToString()),
                                             new XAttribute("dataTypeName", sqlDataReader.GetDataTypeName(t).ToString())
                                    ));
                }
            }

            return rowData;
        }

        public static string GetServiceUrl(SqlConnection sqlConnection)
        {
            if (sqlConnection.State == ConnectionState.Closed)
            {
                sqlConnection.Open();
            }
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandType = CommandType.Text;
            sqlCommand.CommandText = "SELECT Value FROM dbo.Configuration WHERE Id=0";
            return sqlCommand.ExecuteScalar().ToString();
        }
    }
}
